﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using log4net;
using mimosa.Utilities;
using Npgsql;
using NpgsqlTypes;


namespace mimosa.SIPServer
{
    public enum StorageTypes
    {
        Unknown,
        MSSQL,
        Postgresql,
        XML
    }

    public class StorageLayer
    {
        private ILog logger = AppState.logger;
        private StorageTypes m_storageType;
        private string m_dbConnStr = null;

        public Dictionary<string, TelecomInfo> TelecomVendor = new Dictionary<string, TelecomInfo>();

        //private static Hashtable m_connections = new Hashtable();	// {<conn str>, <ArrayList<DBConnectionWrapper>>] This is the connection that will be used by all callers to this object instance.

        public StorageLayer()
        { }

        public StorageLayer(StorageTypes storageType, string dbConnStr)
        {
            m_storageType = storageType;
            m_dbConnStr = dbConnStr;
        }

        public void ExecuteNonQuery(string query)
        {
            ExecuteNonQuery(m_storageType, m_dbConnStr, query);
        }

        public void ExecuteNonQuery(string query, NpgsqlParameter[] parameters)
        {
            try
            {
                if (m_storageType == StorageTypes.Postgresql)
                {
                    NpgsqlConnection connPgsql = null;

                    try
                    {
                        connPgsql = new NpgsqlConnection(m_dbConnStr);
                        connPgsql.Open();
                        NpgsqlCommand command = new NpgsqlCommand(query, connPgsql);

                        if (parameters != null)
                        {
                            for (int i = 0; i < parameters.Length; i++)
                            {
                                command.Parameters.Add(parameters[i]);
                            }
                        }
                        command.ExecuteNonQuery();
                    }
                    catch (Exception npgsqlExcp)
                    {
                        throw new ApplicationException("Exception ExecuteNonQuery (Npgsql Connection Pooling). Original exception type " + npgsqlExcp.GetType() + ". " + npgsqlExcp.Message);
                    }
                    finally
                    {
                        // If using the Npgsql pooling close the connection to place it back in the pool.
                        try
                        {
                            connPgsql.Close();
                        }
                        catch { }
                    }
                }
                else
                {
                    throw new ApplicationException("StorageType " + m_storageType + " not currently supported in ExecuteNonQuery");
                }
            }
            catch (Exception excp)
            {
                //logger.Error("Exception ExecuteNonQuery. " + excp.Message);
                throw excp;
            }
        }

        public void ExecuteNonQuery(string query, object[] parameters, object[] parameternames)
        {
            try
            {
                if (m_storageType == StorageTypes.Postgresql)
                {
                    NpgsqlConnection connPgsql = null;

                    try
                    {
                        connPgsql = new NpgsqlConnection(m_dbConnStr);
                        connPgsql.Open();
                        NpgsqlCommand command = new NpgsqlCommand(query, connPgsql);

                        if (parameters != null)
                        {
                            for (int i = 0; i < parameters.Length; i++)
                            {
                                command.Parameters.Add(new NpgsqlParameter(parameternames[i].ToString(), parameters[i]));
                            }
                        }
                        command.ExecuteNonQuery();
                    }
                    catch (Exception npgsqlExcp)
                    {
                        throw new ApplicationException("Exception ExecuteNonQuery (Npgsql Connection Pooling). Original exception type " + npgsqlExcp.GetType() + ". " + npgsqlExcp.Message);
                    }
                    finally
                    {
                        // If using the Npgsql pooling close the connection to place it back in the pool.
                        try
                        {
                            connPgsql.Close();
                        }
                        catch { }
                    }
                }
                else
                {
                    throw new ApplicationException("StorageType " + m_storageType + " not currently supported in ExecuteNonQuery");
                }
            }
            catch (Exception excp)
            {
                //logger.Error("Exception ExecuteNonQuery. " + excp.Message);
                throw excp;
            }
        }

        public void ExecuteNonQuery(StorageTypes storageType, string dbConnString, string query)
        {
            try
            {
                if (storageType == StorageTypes.Postgresql)
                {
                    NpgsqlConnection connPgsql = null;

                    try
                    {
                        connPgsql = new NpgsqlConnection(dbConnString);
                        connPgsql.Open();
                        NpgsqlCommand command = new NpgsqlCommand(query, connPgsql);
                        command.ExecuteNonQuery();
                    }
                    catch (Exception npgsqlExcp)
                    {
                        throw new ApplicationException("Exception ExecuteNonQuery (Npgsql Connection Pooling). Original exception type " + npgsqlExcp.GetType() + ". " + npgsqlExcp.Message);
                    }
                    finally
                    {
                        // If using the Npgsql pooling close the connection to place it back in the pool.
                        try
                        {
                            connPgsql.Close();
                        }
                        catch { }
                    }
                }
                else
                {
                    throw new ApplicationException("StorageType " + storageType + " not currently supported in ExecuteNonQuery");
                }
            }
            catch (Exception excp)
            {
                //logger.Error("Exception ExecuteNonQuery. " + excp.Message);
                throw excp;
            }
        }

        public object ExecuteScalar(string query)
        {
            return ExecuteScalar(m_storageType, m_dbConnStr, query);
        }

        public object ExecuteScalar(StorageTypes storageType, string dbConnString, string query)
        {
            try
            {
                if (storageType == StorageTypes.Postgresql)
                {
                    NpgsqlConnection connPgsql = null;

                    try
                    {
                        connPgsql = new NpgsqlConnection(dbConnString);
                        connPgsql.Open();
                        NpgsqlCommand command = new NpgsqlCommand(query, connPgsql);
                        return command.ExecuteScalar();
                    }
                    catch (Exception npgsqlExcp)
                    {
                        throw new ApplicationException("Exception ExecuteScalar (Npgsql Connection Pooling). Original exception type " + npgsqlExcp.GetType() + ". " + npgsqlExcp.Message);
                    }
                    finally
                    {
                        // If using the Npgsql pooling close the connection to place it back in the pool.
                        try
                        {
                            connPgsql.Close();
                        }
                        catch { }
                    }
                }
                else
                {
                    throw new ApplicationException("StorageType " + storageType + " not currently supported in ExecuteScalar");
                }
            }
            catch (Exception excp)
            {
                //logger.Error("Exception ExecuteScalar. " + excp.Message);
                throw excp;
            }
        }

        public DataSet GetDataSet(string query)
        {
            return GetDataSet(m_storageType, m_dbConnStr, query, null);
        }


        public DataSet GetDataSet(string query, object[] parameters)
        {
            return GetDataSet(m_storageType, m_dbConnStr, query, parameters);
        }

        public DataSet GetDataSet(string query, object[] parameters, object[] parameternames)
        {
            return GetDataSet(m_storageType, m_dbConnStr, query, parameters, parameternames);
        }

        public DataSet GetDataSet(StorageTypes storageType, string dbConnString, string query, object[] parameters, object[] parameternames)
        {
            try
            {
                if (storageType == StorageTypes.Postgresql)
                {
                    NpgsqlConnection connPgsql = null;

                    try
                    {
                        connPgsql = new NpgsqlConnection(dbConnString);
                        connPgsql.Open();

                        NpgsqlCommand cmd = new NpgsqlCommand(query, connPgsql);
                        if (parameters != null)
                        {
                            for (int i = 0; i < parameters.Length; i++)
                                cmd.Parameters.Add(new NpgsqlParameter(parameternames[i].ToString(), parameters[i]));
                        }

                        NpgsqlDataReader reader = cmd.ExecuteReader();
                        DataSet resultSet = new DataSet();
                        DataTable dt = new DataTable("result");
                        resultSet.Tables.Add(dt);
                        resultSet.Load(reader, LoadOption.OverwriteChanges, dt);
                        reader.Close();
                        return resultSet;
                    }
                    catch (Exception npgsqlExcp)
                    {
                        throw new ApplicationException("Exception GetDataSet (Npgsql Connection Pooling). Original exception type " + npgsqlExcp.GetType() + ". " + npgsqlExcp.Message);
                    }
                    finally
                    {
                        // If using the Npgsql pooling close the connection to place it back in the pool.
                        // If using the Npgsql pooling close the connection to place it back in the pool.
                        try
                        {
                            connPgsql.Close();
                        }
                        catch { }
                    }
                }
                else
                {
                    throw new ApplicationException("StorageType " + storageType + " not currently supported in GetDataSet");
                }
            }
            catch (Exception excp)
            {
                //logger.Error("Exception GetDataSet. " + excp.Message);
                throw excp;
            }
        }

        public DataSet GetDataSet(StorageTypes storageType, string dbConnString, string query, object[] parameters)
        {
            try
            {
                if (storageType == StorageTypes.Postgresql)
                {
                    NpgsqlConnection connPgsql = null;

                    try
                    {
                        connPgsql = new NpgsqlConnection(dbConnString);
                        connPgsql.Open();

                        NpgsqlCommand cmd = new NpgsqlCommand(query, connPgsql);
                        if (parameters != null)
                        {
                            for (int i = 0; i < parameters.Length; i++)
                                cmd.Parameters.Add(new NpgsqlParameter("acct_id", parameters[i]));
                        }

                        NpgsqlDataReader reader = cmd.ExecuteReader();
                        DataSet resultSet = new DataSet();
                        DataTable dt = new DataTable("result");
                        resultSet.Tables.Add(dt);
                        resultSet.Load(reader, LoadOption.OverwriteChanges, dt);
                        reader.Close();
                        return resultSet;
                    }
                    catch (Exception npgsqlExcp)
                    {
                        throw new ApplicationException("Exception GetDataSet (Npgsql Connection Pooling). Original exception type " + npgsqlExcp.GetType() + ". " + npgsqlExcp.Message);
                    }
                    finally
                    {
                        // If using the Npgsql pooling close the connection to place it back in the pool.
                        // If using the Npgsql pooling close the connection to place it back in the pool.
                        try
                        {
                            connPgsql.Close();
                        }
                        catch { }
                    }
                }
                else
                {
                    throw new ApplicationException("StorageType " + storageType + " not currently supported in GetDataSet");
                }
            }
            catch (Exception excp)
            {
                //logger.Error("Exception GetDataSet. " + excp.Message);
                throw excp;
            }
        }
        /// <summary>
        /// Used to determine whethe Npgsql will treat a connection string as pooling or not.
        /// Npgsql assumes a connection string is pooling by default.
        /// </summary>

        /// <summary>
        /// 
        /// </summary>
        /// <param name="column">Needs to be a bit data type from Postgresql.</param>
        /// <returns></returns>
        public bool ConvertToBool(object column)
        {
            //Npgsql, this works now in Npgsql version 0.99.3.
            //return Convert.ToBoolean(column);

            if (column.ToString() == "0")
            {
                return false;
            }
            else if (column.ToString() == "1")
            {
                return true;
            }
            else
            {
                throw new ApplicationException("Couldn't convert a database boolean type in StorageLayer.");
            }
        }

        /// <summary>
        /// 0000 - unreg
        /// 0001 - reg
        /// 0003 - dialog
        /// </summary>
        /// <param name="user"></param>
        /// <param name="status"></param>
        public void UpdateUserStatus(string user, string status)
        {
            string query = string.Format("Update accounts set acct_status = '{0}' where acct_id = '{1}'", status, user);
            ExecuteNonQuery(query);
        }

        public void UpdateExpiredDialog(string user)
        {
            string query = string.Format("Update dialoges set end_time = '99999999999999' where from_user = '{0}' and end_time is null", user);
            ExecuteNonQuery(query);
        }

        public string GetDirtyDialogId(string user)
        {
            string query = string.Format(@"select dialog_id
                                            from dialoges
                                           where from_user = '{0}'
                                            and begin_time in (
			                                                select max(begin_time)
			                                                    from dialoges
			                                                where from_user = '{1}'
			                                                  and end_time is null)", user, user);

            DataSet ds = GetDataSet(query);
            if (ds == null || ds.Tables == null)
                return string.Empty;

            if (ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    return dt.Rows[0][0].ToString();
                }
            }

            return string.Empty;
        }

        public void AddDialog(string dialog_id, string from_user, string to_user)
        {
            string query = string.Format("insert into dialoges (dialog_id, from_user, to_user, begin_time) values ( '{0}', '{1}', '{2}', to_char(now(), 'yyyymmddhh24miss'))", dialog_id, from_user, to_user);
            ExecuteNonQuery(query);
        }

        public void AddDialogError(string dialog_id, string from_user, string to_user, string hangup_type)
        {
            string query = sql.ResourceManager.GetString("INSERT_ERR_DIALOG");
            ExecuteNonQuery(query, new object[] { dialog_id, from_user, to_user, hangup_type },
                                   new object[] { "dialog_id", "from_user", "to_user", "hangup_type" });
        }

        public void InitUserState()
        {
            string query = "update accounts set acct_status = '0000'";
            ExecuteNonQuery(query);
        }

//        public string GetWaitingMidUA(string UID)
//        {
//            string query = string.Format(@"select acct_id
//                                            from accounts
//                                        where acct_type = '0002'
//                                          and telecom_vendor = '{0}'
//                                          and status = '0001'
//                                          ", UID);

//            DataSet ds = GetDataSet(query);
//            if (ds == null || ds.Tables == null)
//                return string.Empty;

//            if (ds.Tables.Count > 0)
//            {
//                DataTable dt = ds.Tables[0];
//                if (dt.Rows.Count > 0)
//                {
//                    return dt.Rows[0][0].ToString();
//                }
//            }

//            return string.Empty;
//        }

        /// <summary>
        /// 클라이언트 계정으로 통신사업자 구하기
        /// </summary>
        /// <param name="accountId"></param>
        /// <returns></returns>
        public string GetClientTelecomVendor(string accountId)
        {
            string query = string.Format(@"select telecom_vendor
                                             from sipaccounts
                                            where sipaccountid = '{0}'
                                              and account_type = '0000'
                                          ", accountId);

            DataSet ds = GetDataSet(query);
            if (ds == null || ds.Tables == null)
                return string.Empty;

            if (ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    return dt.Rows[0][0].ToString();
                }
            }

            return string.Empty;
        }

        /// <summary>
        /// 서버 시작시 데이터를 로딩한다.
        /// </summary>
        public void LoadTelecomVendor()
        {
            TelecomVendor.Clear();
            string query = sql.ResourceManager.GetString("GET_TELECOM");
            DataSet ds = GetDataSet(query);
            if (ds == null || ds.Tables == null)
                return;

            if (ds.Tables.Count > 0)
            {
                
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        TelecomInfo telecomInfo = new TelecomInfo();
                        telecomInfo.UID = row[0].ToString();
                        telecomInfo.Vendor = row[1].ToString();
                        telecomInfo.PreNumber = row[2].ToString();

                        if (!TelecomVendor.ContainsKey(telecomInfo.UID))
                            TelecomVendor.Add(telecomInfo.UID, telecomInfo);
                    }
                }
            }
        }

        public string GetAcctStatus(string accountId)
        {
            DataSet ds = GetDataSet(sql.ResourceManager.GetString("GET_ACCT_STATUS"), new object[] { accountId }, new object[] { "acct_id" });
            if (ds == null || ds.Tables == null)
                return string.Empty;

            if (ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    return dt.Rows[0][0].ToString();
                }
            }

            return string.Empty;
        }
    }

    public class TelecomInfo
    {
        public string Vendor
        {
            get;
            set;
        }

        public string UID
        {
            get;
            set;
        }

        public string PreNumber
        {
            get;
            set;
        }
    }

}
